﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using MISAPP.Common;
using System.Data.Common;
using System.Transactions;

namespace InsideDL.MISData
{
    public class DataMatrix
    {
        protected System.Data.IDbConnection cn = null;

        protected string[] lines = null;

        protected object getCSVDataByRecordSet(string p_sql, string p_path)
        {
            string connectionString =
                "Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=" + p_path
                   + "\\;Extensions=asc,csv,tab,txt;Persist Security Info=False";
            //ADODB.ConnectionClass adoCN = new ADODB.ConnectionClass();
            //ADODB.RecordsetClass adoRS = new ADODB.RecordsetClass();
            //adoCN.Open(connectionString, string.Empty, string.Empty, 0);
            //adoCN.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
            //adoRS.Open(p_sql, adoCN, ADODB.CursorTypeEnum.adOpenDynamic
            //    , ADODB.LockTypeEnum.adLockPessimistic, 1);
            //return adoRS;
            return getRecordSet(p_sql, connectionString);
        }

        protected System.Data.DataTable getCSVData(string p_sql, string p_path)
        {
            string connectionString;
            connectionString = string.Format(
                      @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}; Extended Properties=""TEXT;HDR=Yes;FMT=Delimited"";"
                      , p_path);
            return GetData(p_sql, connectionString, "CSV");
        }

        public System.Data.DataTable GetData(string p_sql
           , string p_connectionString, string p_tableName)
        {
            OleDbConnection cn = new OleDbConnection();
            //System.Data.DataSet CSVDataSet = new System.Data.DataSet();
            cn.ConnectionString = p_connectionString; //strPath是檔案所在的資料夾
            OleDbCommand cmd = cn.CreateCommand();
            cn.Open();
            cmd.CommandText = p_sql;
            System.Data.DataTable dt = new System.Data.DataTable();

            dt.Load(cmd.ExecuteReader());
            //OleDbDataAdapter CSVAdapter = new OleDbDataAdapter(p_sql, cn);
            //CSVAdapter.Fill(CSVDataSet, p_tableName);
            cn.Close();
            return dt;
        }

        protected System.Data.DataTable getExcelData(string p_sql, string p_path)
        {
            string connection = string.Format(
                @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""", p_path);
            return GetData(p_sql, connection, p_path);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="p_sql"></param>
        /// <param name="p_path">完整路徑+檔名</param>
        /// <returns></returns>
        protected object getExcelDataByRecordSet(string p_sql,string p_path)
        {
            string cn = "Driver={Microsoft Excel Driver (*.xls)};DBQ=" + p_path + ";Readonly=False";

            return getRecordSet(p_sql, cn);            
        }

        protected object getRecordSet(string p_sql,string p_connection)
        {
            ADODB.ConnectionClass adoCN = new ADODB.ConnectionClass();
            ADODB.RecordsetClass adoRS = new ADODB.RecordsetClass();
            adoCN.Open(p_connection, string.Empty, string.Empty, 0);
            adoCN.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
            adoRS.Open(p_sql, adoCN, ADODB.CursorTypeEnum.adOpenDynamic
                , ADODB.LockTypeEnum.adLockPessimistic, 1);
            return adoRS;        
        }

        protected DbCommand getCommand()
        {
            //return DAL3.GetCommand();       
            return DAL.GetCommand();       
        }

        protected DbParameter getParameter(string p_name, DbType p_type
            ,object p_value,DbParameter p_para)
        {
            return DAL.GetParameter(p_name, p_type, p_value, p_para);
            //return DAL3.GetParameter(p_name, p_type, p_value,p_para);
        }

        protected int executeSQL(DbCommand p_cmd)
        {
            //return DAL3.ExecuteNoneQuery(p_cmd);
            return DAL.ExecuteNoneQuery(p_cmd);
        }

        protected DataTable getSQLData(DbCommand p_cmd)
        {
            //return DAL3.GetDataTable(p_cmd);
            return DAL.GetDataTable(p_cmd);
        }

        protected void upLoad(string p_sql, string[] p_paraNames
            , char[] p_splits,DbType[] p_dbTypes)
        {

            string[] flds;
            flds = null;
            int count;
            count = 0;

            using (TransactionScope scop = new TransactionScope())
            {
                //System.Data.Common.DbCommand cmd = null;
                System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection();
                //cn.ConnectionString = @"Data Source=172.16.76.57\SQLEXPRESS;Initial Catalog=MISDB;User ID=MISDB;Password=MISDB1234;Connection Timeout=0;Connection Lifetime=100";
                cn.ConnectionString = @"Data Source=172.16.76.57;Initial Catalog=MISDB;User ID=misdb;Password=misdb1234;Connection Timeout=3600";
                cn.Open();
                System.Data.SqlClient.SqlCommand cmd = null;
                try
                {
                    foreach (string ln in lines)
                    {
                        count++;
                        //if (count.Equals(1436))
                        //{
                        //    System.Diagnostics.Debugger.Break();
                        //}
                        flds = ln.Split(p_splits,
                            StringSplitOptions.RemoveEmptyEntries);
                        //cmd = getCommand();
                        cmd = cn.CreateCommand();
                        cmd.CommandText = p_sql;
                        for (int i = 0; i < flds.Length; i++)
                        {
                            cmd.Parameters.Add(getParameter(p_paraNames[i]
                                , p_dbTypes[i], flds[i], cmd.CreateParameter()));
                        }
                        //executeSQL(cmd);
                        cmd.ExecuteNonQuery();

                    }
                    scop.Complete();
                }
                catch (Exception ex)
                {
                    throw new Exception(string.Format(
                        " Upload ERROR:{0}", ex.Message));
                }
                finally
                {
                    cn.Close();
                    cn.Dispose();
                }
            }            
        }

        protected virtual void check(string p_str)
        {
        }

        //public virtual void Upload(object[] p_para)
        //{ 
            
        //}
    }
}
